select ro.id id, ro.flat_details_id flatDetailsId,
        ro.external_id externalId, fd.kitchen_square kitchenSquare, fd.max_level maxLevel,
        fd.tel_numbers telNumbers, fd.living_square livingSquare, fd.common_square commonSquare,
        fd.level level, fd.address address, fd.balcony_num balconyNum, fd.adv_info advInfo, fd.rooms rooms,
        p.state state, p.date date, p.price_value priceValue,

         ( select if(p.price_value=sp.price_value,0,if(p.price_value>sp.price_value,1,-1))
                from price sp
                where sp.realty_object_id = ro.id and sp.date < p.date
            order by sp.date desc limit 1
         ) dynamic
from realty_object ro
    join flat_details fd on fd.id = ro.flat_details_id
    join price p on p.realty_object_id = ro.id
     <%
    import org.apache.commons.lang.StringUtils;
    print(' where  date(p.date) = (select max(date(ssp.date)) from price ssp where ro.id = ssp.realty_object_id and');
    if(StringUtils.isNotEmpty(dateInterval1) && StringUtils.isNotEmpty(dateInterval2)){
        print(" date(ssp.date) >= date('${dateInterval1}') and date(ssp.date) <= date('${dateInterval2}') ");
    }else{
        if(StringUtils.isNotEmpty(dateInterval1))
            print(" date(ssp.date) >= date('${dateInterval1}') ")
        if(StringUtils.isNotEmpty(dateInterval2))
            print(" date(ssp.date) <= date('${dateInterval2}') ")
        if(StringUtils.isEmpty(dateInterval1) && StringUtils.isEmpty(dateInterval2)){
            print(" date(ssp.date) = (select max(date(date)) from price) ")
        }
    }
    print(") ");
 %>
order by p.state desc, ro.external_id asc
